package com.example.tourism.mapper;


import com.example.tourism.pojo.mo.HotelOrderMO;
import com.example.tourism.pojo.po.*;
import com.example.tourism.pojo.vo.req.GetOrderListVo;
import com.example.tourism.pojo.vo.res.MyCouponRes;
import com.example.tourism.pojo.vo.res.OrderTime;
import com.example.tourism.pojo.vo.res.OrderVo;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface OrderMapper {

    @Select("SELECT h.id, h.hotel_name, h.hotel_address, r.price " +
            "FROM hotel h " +
            "INNER JOIN room r ON h.user_id = r.user_id " +
            "WHERE r.id = #{roomId}")
    HotelOrderMO getHotelMOByRoomId(String roomId);

    @Select("select * from customer_info where user_id = #{userId}")
    Customer getCustomers(String userId);

    @Select("select * from orders where order_uid = #{orderUid}")
    Orders selectOrderByOrderUid(String orderUid);

    @Insert("insert into orders (id,order_uid, create_time, price, type, status, customer_name, room_id, user_id,check_in,check_out,couponId,email,phone,note)  values(null,#{orderUid}, #{createTime}, #{price}, #{type}, #{status}, #{customerName}, #{roomId}, #{userId},#{checkIn},#{checkOut},#{couponId},#{email},#{phone},#{note}) ")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertOrders(Orders orders);

    @Select("update orders set status = 1 where order_uid = #{orderUid}")
    void updateOrderStatus(String orderUid);


    @Select("select check_in,check_out from orders where room_id = #{roomId}  and status != 2")
    List<OrderTime> selectOverOrdersTime(String roomId);

    @Update("UPDATE orders set status = 2 where order_uid = #{orderUid} AND status = 0")
    void updateStatus(String orderUid);

    @Select("SELECT COUNT(*) FROM orders " +
            "WHERE room_id = #{roomId} " +
            "AND NOT (check_out < #{checkIn} OR check_in > #{checkOut})" +
            "AND status != 2")
    int selectCountByRoomId(@Param("roomId") String roomId,
                            @Param("checkIn") LocalDate checkIn,
                            @Param("checkOut") LocalDate checkOut);

    // 分页查询 订单列表
    @Select("SELECT order_uid ,customer_name, create_time, price, status, check_in, check_out FROM orders WHERE room_id IN (SELECT id FROM room WHERE user_id = #{userId}) AND status = 1 ORDER BY create_time DESC LIMIT #{start}, #{pageSize}")
    List<OrderVo> selectHotelOrderList(@Param("userId") String userId, @Param("start") int start, @Param("pageSize")  int pageSize);

    @Select("SELECT COUNT(*) FROM orders WHERE room_id in (SELECT id FROM room WHERE user_id = #{userId}) AND  status = 1")
    int selectHotelOrderCount(String userId);

    @Select("SELECT order_uid , customer_name, create_time, price, status, check_in, check_out FROM orders WHERE room_id IN (SELECT id FROM room WHERE user_id = #{userId}) AND status = 1 AND (customer_name LIKE CONCAT('%', #{searchKey}, '%') OR check_in LIKE CONCAT('%', #{searchKey}, '%') OR check_out LIKE CONCAT('%', #{searchKey}, '%')) ORDER BY create_time DESC LIMIT #{start}, #{pageSize}")
    List<OrderVo> selectHotelOrderListByKey(@Param("userId") String userId, @Param("searchKey") String searchKey, @Param("start") int start, @Param("pageSize")  int pageSize);

    @Select("SELECT COUNT(*) FROM orders WHERE room_id in (SELECT id FROM room WHERE user_id = #{userId}) AND  status = 1 AND (customer_name LIKE CONCAT('%', #{searchKey}, '%') OR check_in LIKE CONCAT('%', #{searchKey}, '%') OR check_out LIKE CONCAT('%', #{searchKey}, '%'))")
    int selectHotelOrderCountByKey(@Param("userId") String userId,@Param("searchKey") String searchKey);

    @Select("SELECT * FROM orders WHERE user_id = #{userId}  AND (customer_name LIKE CONCAT('%', #{searchKey}, '%') OR check_in LIKE CONCAT('%', #{searchKey}, '%') OR check_out LIKE CONCAT('%', #{searchKey}, '%'))  ORDER BY create_time DESC LIMIT #{start}, #{pageSize} ")
    List<Orders> selectClientOrderListByKey(@Param("userId") String userId, @Param("searchKey") String searchKey, @Param("start") int start, @Param("pageSize")  int pageSize);

    @Select("SELECT *  FROM orders WHERE user_id = #{userId}  ORDER BY create_time DESC Limit #{start}, #{pageSize}")
    List<Orders> selectClientOrderList(@Param("userId") String userId, @Param("start") int start, @Param("pageSize")  int pageSize);

    @Select("SELECT COUNT(*) FROM orders WHERE user_id = #{userId}")
    int selectClientOrderCount(String userId);

    @Select("SELECT COUNT(*) FROM orders WHERE user_id = #{userId} AND (customer_name LIKE CONCAT('%', #{searchKey}, '%') OR check_in LIKE CONCAT('%', #{searchKey}, '%') OR check_out LIKE CONCAT('%', #{searchKey}, '%'))")
    int selectClientOrderCountByKey(String userId, String searchKey);

    @Select("SELECT * FROM room WHERE id = #{roomId}")
    Room getRoomByOrderUid(String orderUid);

    @Update("UPDATE orders SET status = #{i} WHERE order_uid = #{orderUid}")
    int updateOrderCancelStatus(String orderUid, int i);

    @Select("SELECT  COUNT(*) FROM comment WHERE order_uid = #{orderUid}")
    Integer selectCommentByOrderUid(String orderUid);

    @Select("SELECT h.id FROM hotel h " +
            "JOIN room r ON h.user_id = r.user_id " +
            "JOIN orders o ON r.id = o.room_id " +
            "WHERE o.order_uid = #{orderUid};")
    Integer selectHotelIdByOrderUid(String orderUid);

    @Insert("INSERT INTO comment (id, order_uid, message, time, rating, hotel_id) VALUES (null,  #{orderUid}, #{message}, #{time}, #{rating}, #{hotelId})")
    int insertComment(Comment commentPo);

    @Select("SELECT id FROM hotel WHERE user_id = #{userId}")
    String selectHotelIdByUserId(String userId);

    @Insert("INSERT INTO coupon (coupon_id, hotel_id,name,discount,start_date,end_date,min_spend,issue_date,quantity) VALUES (NULL, #{hotelId}, #{name}, #{discount}, #{startDate}, #{endDate}, #{minSpend}, #{issueDate}, #{quantity})")
    @Options(useGeneratedKeys = true, keyProperty = "couponId")
    int insertCoupon(Coupon coupon);

    @Select("SELECT * FROM coupon WHERE hotel_id = #{hotelId}")
    List<Coupon> getCoupons(String hotelId);

    @Update("UPDATE coupon SET quantity = #{quantity} WHERE coupon_id = #{couponId} AND quantity > 0")
    int updateCoupon( String couponId, Integer  quantity);

    @Select("SELECT quantity FROM coupon WHERE coupon_id = #{couponId}")
    int selectCouponQuantity(String couponId);

    @Select("SELECT COUNT(*) FROM user_coupon WHERE coupon_id = #{couponId} AND user_id = #{userId}")
    int selectCouponCount(@Param("couponId") String couponId,@Param("userId")  String userId);

    @Insert("INSERT INTO user_coupon (user_id, coupon_id,status) VALUES (#{userId}, #{couponId},0)")
    int insertUserCoupon(@Param("userId") String userId, @Param("couponId") String couponId);

    @Select("SELECT id FROM hotel WHERE user_id =(select user_id from room where id = #{roomId})")
    String selectHotelIdByRoomId(String roomId);

    @Select("SELECT coupon_id FROM user_coupon WHERE user_id = #{userId} AND coupon_id in (SELECT coupon_id FROM coupon WHERE hotel_id = #{hotelId}) AND status = 0")
    List<Integer> getUserCoupons(@Param("userId") String userId, @Param("hotelId") String hotelId);

    @Select("SELECT * FROM coupon WHERE coupon_id = #{i}")
    Coupon getCouponById(Integer i);

    @Update("UPDATE user_coupon set status = 1 WHERE coupon_id = #{couponId} AND user_id = #{userId}")
    int updateCouponStatus(@Param("couponId") Integer couponId, @Param("userId") String userId);

    @Select("SELECT coupon.coupon_id,coupon.name, " +
            "hotel.hotel_name AS shopName, " +
            "    coupon.discount, " +
            "    coupon.end_date, " +
            "    coupon.issue_date,  " +
            "    CASE user_coupon.status " +
            "        WHEN 0 THEN '未使用' " +
            "        WHEN 1 THEN '已使用' " +
            "    END AS status " +
            "FROM " +
            "    coupon " +
            "JOIN " +
            "    user_coupon ON coupon.coupon_id = user_coupon.coupon_id " +
            "JOIN " +
            "    hotel ON coupon.hotel_id = hotel.id " +
            "WHERE " +
            "    user_coupon.user_id = 1;")
    List<MyCouponRes> getMyCoupons(String userId);

    @Select("SELECT quantity FROM coupon WHERE coupon_id = #{couponId}")
    Long queryStockByProductId(String couponId);

}

